/**
 * Copyright (c) 2010-2020 Contributors to the openHAB project
 *
 * See the NOTICE file(s) distributed with this work for additional
 * information.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0
 *
 * SPDX-License-Identifier: EPL-2.0
 */
package org.openhab.persistence.mysql.internal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Formatter;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.eclipse.jdt.annotation.NonNull;
import org.eclipse.jdt.annotation.NonNullByDefault;
import org.eclipse.jdt.annotation.Nullable;
import org.openhab.core.items.GroupItem;
import org.openhab.core.items.Item;
import org.openhab.core.items.ItemNotFoundException;
import org.openhab.core.items.ItemRegistry;
import org.openhab.core.library.items.ColorItem;
import org.openhab.core.library.items.ContactItem;
import org.openhab.core.library.items.DateTimeItem;
import org.openhab.core.library.items.DimmerItem;
import org.openhab.core.library.items.NumberItem;
import org.openhab.core.library.items.RollershutterItem;
import org.openhab.core.library.items.SwitchItem;
import org.openhab.core.library.types.DateTimeType;
import org.openhab.core.library.types.DecimalType;
import org.openhab.core.library.types.HSBType;
import org.openhab.core.library.types.OnOffType;
import org.openhab.core.library.types.OpenClosedType;
import org.openhab.core.library.types.PercentType;
import org.openhab.core.library.types.StringType;
import org.openhab.core.persistence.FilterCriteria;
import org.openhab.core.persistence.FilterCriteria.Ordering;
import org.openhab.core.persistence.HistoricItem;
import org.openhab.core.persistence.PersistenceItemInfo;
import org.openhab.core.persistence.PersistenceService;
import org.openhab.core.persistence.QueryablePersistenceService;
import org.openhab.core.persistence.strategy.PersistenceStrategy;
import org.openhab.core.types.State;
import org.openhab.core.types.UnDefType;
import org.osgi.framework.BundleContext;
import org.osgi.service.component.annotations.Activate;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.ConfigurationPolicy;
import org.osgi.service.component.annotations.Deactivate;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * This is the implementation of the mySQL {@link PersistenceService}.
 *
 * Data is persisted with the following conversions:
 *
 * Item-Type Data-Type MySQL-Type
 * ========= ========= ==========
 * ColorItem HSBType CHAR(25)
 * ContactItem OnOffType CHAR(6)
 * DateTimeItem DateTimeType DATETIME
 * DimmerItem PercentType TINYINT
 * NumberItem DecimalType DOUBLE
 * RollershutterItem PercentType TINYINT
 * StringItem StringType VARCHAR(20000)
 * SwitchItem OnOffType CHAR(3)
 *
 * In the store method, type conversion is performed where the default type for
 * an item is not as above. For example, DimmerType can return OnOffType, so to
 * keep the best resolution, we store as a number in SQL and convert to
 * DecimalType before persisting to MySQL.
 *
 * @author Henrik Sjöstrand - Initial contribution
 * @author Thomas.Eichstaedt-Engelen - Enhancements
 * @author Chris Jackson - Enhancements
 * @author Helmut Lehmeyer - Enhancements
 */
@NonNullByDefault
@Component(service = { PersistenceService.class,
        QueryablePersistenceService.class }, configurationPid = "org.openhab.mysql", configurationPolicy = ConfigurationPolicy.REQUIRE)
public class MysqlPersistenceService implements QueryablePersistenceService {

    private static final Pattern EXTRACT_CONFIG_PATTERN = Pattern.compile("^(.*?)\\.([0-9.a-zA-Z]+)$");

    private static final Logger logger = LoggerFactory.getLogger(MysqlPersistenceService.class);

    private String driverClass = "com.mysql.jdbc.Driver";
    private @NonNullByDefault({}) String url;
    private @NonNullByDefault({}) String user;
    private @NonNullByDefault({}) String password;

    private boolean initialized = false;

    @Reference
    protected @NonNullByDefault({}) ItemRegistry itemRegistry;

    // Error counter - used to reconnect to database on error
    private int errCnt;
    private int errReconnectThreshold = 0;

    private int waitTimeout = -1;

    // Time used for persisting items, False: MySQL Server time (default), True: openHAB Server time
    private boolean localtime = false;

    private @NonNullByDefault({}) Connection connection = null;

    private Map<String, @Nullable String> sqlTables = new HashMap<>();
    private Map<String, @Nullable String> sqlTypes = new HashMap<>();

    /**
     * Initialise the type array
     * If other Types like DOUBLE or INT needed for serialisation it can be set in openhab.cfg
     */
    @Activate
    public void activate(final BundleContext bundleContext, final Map<String, Object> config) {
        sqlTypes.put("CALLITEM", "VARCHAR(200)");
        sqlTypes.put("COLORITEM", "VARCHAR(70)");
        sqlTypes.put("CONTACTITEM", "VARCHAR(6)");
        sqlTypes.put("DATETIMEITEM", "DATETIME");
        sqlTypes.put("DIMMERITEM", "TINYINT");
        sqlTypes.put("LOCATIONITEM", "VARCHAR(30)");
        sqlTypes.put("NUMBERITEM", "DOUBLE");
        sqlTypes.put("ROLLERSHUTTERITEM", "TINYINT");
        sqlTypes.put("STRINGITEM", "VARCHAR(20000)");
        sqlTypes.put("SWITCHITEM", "CHAR(3)");

        Iterator<String> keys = config.keySet().iterator();
        while (keys.hasNext()) {
            String key = keys.next();

            Matcher matcher = EXTRACT_CONFIG_PATTERN.matcher(key);

            if (!matcher.matches()) {
                continue;
            }

            matcher.reset();
            matcher.find();

            if (!matcher.group(1).equals("sqltype")) {
                continue;
            }

            String itemType = matcher.group(2).toUpperCase() + "ITEM";
            String value = (String) config.get(key);

            sqlTypes.put(itemType, value);
        }

        disconnectFromDatabase();

        url = (String) config.get("url");
        if (StringUtils.isBlank(url)) {
            logger.warn("The mySQL database URL is missing. Please configure the url parameter in the configuration.");
            return;
        }

        user = (String) config.get("user");
        if (StringUtils.isBlank(user)) {
            logger.warn("The mySQL user is missing. Please configure the user parameter in the configuration.");
            return;
        }

        password = (String) config.get("password");
        if (StringUtils.isBlank(password)) {
            logger.warn(
                    "The mySQL password is missing; attempting to connect without password. To specify a password, configure the password parameter in the configuration.");
        }

        String tmpString = (String) config.get("reconnectCnt");
        if (StringUtils.isNotBlank(tmpString)) {
            errReconnectThreshold = Integer.parseInt(tmpString);
        }

        tmpString = (String) config.get("waitTimeout");
        if (StringUtils.isNotBlank(tmpString)) {
            waitTimeout = Integer.parseInt(tmpString);
        }

        tmpString = (String) config.get("localtime");
        if (StringUtils.isNotBlank(tmpString)) {
            localtime = Boolean.parseBoolean(tmpString);
        }

        // reconnect to the database in case the configuration has changed.
        connectToDatabase();

        // connection has been established ... initialization completed!
        initialized = true;

        logger.debug("mySQL configuration complete.");
    }

    @Deactivate
    public void deactivate(final int reason) {
        logger.debug("mySQL persistence bundle stopping. Disconnecting from database.");
        disconnectFromDatabase();
    }

    @Override
    public String getId() {
        return "mysql";
    }

    @Override
    public String getLabel(@Nullable Locale locale) {
        return "MySQL";
    }

    /**
     *
     * @param i
     * @return
     */
    private @Nullable String getItemType(Item i) {
        Item item = i;
        if (i instanceof GroupItem) {
            item = ((GroupItem) i).getBaseItem();
            if (item == null) {// if GroupItem:<ItemType> is not defined in *.items using StringType
                logger.debug(
                        "mySQL: Cannot detect ItemType for {} because the GroupItems' base type isn't set in *.items file.",
                        i.getName());
                item = ((GroupItem) i).getMembers().iterator().next();
            }
        }
        String itemType = item.getClass().getSimpleName().toUpperCase();
        if (sqlTypes.get(itemType) == null) {
            logger.debug("mySQL: No sqlType found for ItemType {}, use ItemType STRINGITEM ({}) as Fallback for {}",
                    itemType, sqlTypes.get("STRINGITEM"), i.getName());
            return sqlTypes.get("STRINGITEM");
        }

        logger.debug("mySQL: Use ItemType {} ({}) for Item {}", itemType, sqlTypes.get(itemType), i.getName());
        return sqlTypes.get(itemType);
    }

    private @Nullable String getTable(Item item) {
        String sqlCmd = null;
        int rowId = 0;

        String itemName = item.getName();
        String tableName = sqlTables.get(itemName);

        // Table already exists - return the name
        if (tableName != null) {
            return tableName;
        }

        logger.debug("mySQL: no Table found for itemName={} get:{}", itemName, sqlTables.get(itemName));

        sqlCmd = new String("INSERT INTO Items (ItemName) VALUES (?)");

        // Create a new entry in the Items table. This is the translation of
        // item name to table
        try (PreparedStatement statement = connection.prepareStatement(sqlCmd, Statement.RETURN_GENERATED_KEYS)) {
            statement.setString(1, itemName);
            statement.executeUpdate();

            ResultSet resultSet = statement.getGeneratedKeys();
            if (resultSet != null && resultSet.next()) {
                rowId = resultSet.getInt(1);
            }

            if (rowId == 0) {
                throw new SQLException("mySQL: Creating table for item '{}' failed.", itemName);
            }

            // Create the table name
            tableName = new String("Item" + rowId);
            logger.debug("mySQL: new item {} is Item{}", itemName, rowId);
        } catch (SQLException e) {
            errCnt++;
            logger.error("mySQL: Could not create entry for '{}' in table 'Items' with statement '{}': {}", itemName,
                    sqlCmd, e.getMessage());
        }

        // An error occurred adding the item name into the index list!
        if (tableName == null) {
            logger.error("mySQL: tableName was null");
            return null;
        }

        String mysqlType = getItemType(item);

        // We have a rowId, create the table for the data
        sqlCmd = new String(
                "CREATE TABLE " + tableName + " (Time DATETIME, Value " + mysqlType + ", PRIMARY KEY(Time));");
        logger.debug("mySQL: query: {}", sqlCmd);

        try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
            statement.executeUpdate();

            logger.debug("mySQL: Table created for item '{}' with datatype {} in SQL database.", itemName, mysqlType);
            sqlTables.put(itemName, tableName);
        } catch (Exception e) {
            errCnt++;
            logger.error("mySQL: Could not create table for item '{}' with statement '{}': {}", itemName, sqlCmd,
                    e.getMessage());
        }

        // Check if the new entry is in the table list
        // If it's not in the list, then there was an error and we need to do some tidying up
        // The item needs to be removed from the index table to avoid duplicates
        if (sqlTables.get(itemName) == null) {
            logger.error("mySQL: Item '{}' was not added to the table - removing index", itemName);
            sqlCmd = new String("DELETE FROM Items WHERE ItemName=?");
            logger.debug("mySQL: query: {}", sqlCmd);

            try (PreparedStatement statement = connection.prepareStatement(sqlCmd);) {
                statement.setString(1, itemName);
                statement.executeUpdate();
            } catch (Exception e) {
                errCnt++;

                logger.error("mySQL: Could not remove index for item '{}' with statement '{}': ", itemName, sqlCmd, e);
            }
        }

        return tableName;
    }

    /**
     * @{inheritDoc
     */
    @Override
    public void store(Item item, @Nullable String alias) {
        // Don't log undefined/uninitialised data
        if (item.getState() instanceof UnDefType) {
            return;
        }

        // If we've not initialised the bundle, then return
        if (initialized == false) {
            return;
        }

        // Connect to mySQL server if we're not already connected
        if (!isConnected()) {
            connectToDatabase();
        }

        // If we still didn't manage to connect, then return!
        if (!isConnected()) {
            logger.warn(
                    "mySQL: No connection to database. Cannot persist item '{}'! "
                            + "Will retry connecting to database when error count:{} equals errReconnectThreshold:{}",
                    item, errCnt, errReconnectThreshold);
            return;
        }

        // Get the table name for this item
        String tableName = getTable(item);
        if (tableName == null) {
            logger.error("Unable to store item '{}'.", item.getName());
            return;
        }

        // Do some type conversion to ensure we know the data type.
        // This is necessary for items that have multiple types and may return their
        // state in a format that's not preferred or compatible with the MySQL type.
        // eg. DimmerItem can return OnOffType (ON, OFF), or PercentType (0-100).
        // We need to make sure we cover the best type for serialisation.
        String value;
        if (item instanceof ColorItem) {
            value = item.getStateAs(HSBType.class).toString();
        } else if (item instanceof RollershutterItem) {
            value = item.getStateAs(PercentType.class).toString();
        } else {
            /*
             * !!ATTENTION!!
             *
             * 1.
             * DimmerItem.getStateAs(PercentType.class).toString() always returns 0
             * RollershutterItem.getStateAs(PercentType.class).toString() works as expected
             *
             * 2.
             * (item instanceof ColorItem) == (item instanceof DimmerItem) = true
             * Therefore for instance tests ColorItem always has to be tested before DimmerItem
             *
             * !!ATTENTION!!
             */

            // All other items should return the best format by default
            value = item.getState().toString();
        }

        // Get current timestamp
        long timeNow = Calendar.getInstance().getTimeInMillis();
        Timestamp timestamp = new Timestamp(timeNow);

        String sqlCmd = null;
        PreparedStatement statement = null;
        try {
            if (localtime) {
                sqlCmd = new String(
                        "INSERT INTO " + tableName + " (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;");
                statement = connection.prepareStatement(sqlCmd);
                statement.setTimestamp(1, timestamp);
                statement.setString(2, value);
                statement.setString(3, value);
            } else {
                sqlCmd = new String(
                        "INSERT INTO " + tableName + " (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;");
                statement = connection.prepareStatement(sqlCmd);
                statement.setString(1, value);
                statement.setString(2, value);
            }

            statement.executeUpdate();

            logger.debug("mySQL: Stored item '{}' as '{}'[{}] in SQL database at {}.", item.getName(),
                    item.getState().toString(), value, timestamp.toString());
            logger.debug("mySQL: query: {}", sqlCmd);

            // Success
            errCnt = 0;
        } catch (Exception e) {
            errCnt++;

            logger.error("mySQL: Could not store item '{}' in database with statement '{}': {}", item.getName(), sqlCmd,
                    e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception hidden) {
                }
            }
        }
    }

    @Override
    public void store(Item item) {
        store(item, null);
    }

    /**
     * Checks if we have a database connection
     *
     * @return true if connection has been established, false otherwise
     */
    private boolean isConnected() {
        // Check if connection is valid
        try {
            if (connection != null && !connection.isValid(5000)) {
                errCnt++;
                logger.error("mySQL: Connection is not valid!");
            }
        } catch (SQLException e) {
            errCnt++;

            logger.error("mySQL: Error while checking connection.", e);
        }

        // Error check. If we have 'errReconnectThreshold' errors in a row, then
        // reconnect to the database
        if (errReconnectThreshold != 0 && errCnt >= errReconnectThreshold) {
            logger.error("mySQL: Error count exceeded {}. Disconnecting database.", errReconnectThreshold);
            disconnectFromDatabase();
        }
        return connection != null;
    }

    /**
     * Connects to the database
     */
    private void connectToDatabase() {
        try {
            // Reset the error counter
            errCnt = 0;

            logger.debug("mySQL: Attempting to connect to database {}", url);
            Class.forName(driverClass).newInstance();
            connection = DriverManager.getConnection(url, user, password);
            logger.debug("mySQL: Connected to database {}", url);

            int result;
            try (Statement st = connection.createStatement()) {
                result = st.executeUpdate("SHOW TABLES LIKE 'Items'");
            }
            if (waitTimeout != -1) {
                logger.debug("mySQL: Setting wait_timeout to {} seconds.", waitTimeout);
                try (Statement st = connection.createStatement()) {
                    st.executeUpdate("SET SESSION wait_timeout=" + waitTimeout);
                }
            }
            if (result == 0) {
                try (Statement st = connection.createStatement()) {
                    st.executeUpdate(
                            "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));",
                            Statement.RETURN_GENERATED_KEYS);
                }
            }

            // Retrieve the table array
            try (Statement st = connection.createStatement()) {
                // Turn use of the cursor on.
                st.setFetchSize(50);
                try (ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items")) {
                    while (rs.next()) {
                        sqlTables.put(rs.getString(2), "Item" + rs.getInt(1));
                    }
                }
            }
        } catch (Exception e) {
            logger.error("mySQL: Failed connecting to the SQL database using: driverClass={}, url={}, user={}",
                    driverClass, url, user, e);
        }
    }

    /**
     * Disconnects from the database
     */
    private void disconnectFromDatabase() {
        if (connection != null) {
            try {
                connection.close();
                logger.debug("mySQL: Disconnected from database {}", url);
            } catch (Exception e) {
                logger.error("mySQL: Failed disconnecting from the SQL database.", e);
            }
            connection = null;
        }
    }

    /**
     * Formats the given <code>alias</code> by utilizing {@link Formatter}.
     *
     * @param alias
     *            the alias String which contains format strings
     * @param values
     *            the values which will be replaced in the alias String
     *
     * @return the formatted value. All format strings are replaced by
     *         appropriate values
     * @see java.util.Formatter for detailed information on format Strings.
     */
    protected String formatAlias(String alias, Object... values) {
        return String.format(alias, values);
    }

    @Override
    public Iterable<HistoricItem> query(FilterCriteria filter) {
        if (!initialized) {
            logger.debug("Query aborted on item {} - mySQL not initialised!", filter.getItemName());
            return Collections.emptyList();
        }

        if (!isConnected()) {
            connectToDatabase();
        }

        if (!isConnected()) {
            logger.debug("Query aborted on item {} - mySQL not connected!", filter.getItemName());
            return Collections.emptyList();
        }

        SimpleDateFormat mysqlDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        // Get the item name from the filter
        // Also get the Item object so we can determine the type
        Item item = null;
        String itemName = filter.getItemName();
        logger.debug("mySQL query: item is {}", itemName);
        try {
            if (itemRegistry != null) {
                item = itemRegistry.getItem(itemName);
            }
        } catch (ItemNotFoundException e1) {
            logger.error("Unable to get item type for {}", itemName);

            // Set type to null - data will be returned as StringType
            item = null;
        }

        if (item instanceof GroupItem) {
            // For Group Items is BaseItem needed to get correct Type of Value.
            item = GroupItem.class.cast(item).getBaseItem();
        }

        String table = sqlTables.get(itemName);
        if (table == null) {
            logger.error("mySQL: Unable to find table for query '{}'.", itemName);
            return Collections.emptyList();
        }

        String filterString = new String();

        if (filter.getBeginDate() != null) {
            if (filterString.isEmpty()) {
                filterString += " WHERE";
            } else {
                filterString += " AND";
            }
            filterString += " TIME>'" + mysqlDateFormat.format(filter.getBeginDate()) + "'";
        }
        if (filter.getEndDate() != null) {
            if (filterString.isEmpty()) {
                filterString += " WHERE";
            } else {
                filterString += " AND";
            }
            filterString += " TIME<'" + mysqlDateFormat.format(filter.getEndDate().getTime()) + "'";
        }

        if (filter.getOrdering() == Ordering.ASCENDING) {
            filterString += " ORDER BY Time ASC";
        } else {
            filterString += " ORDER BY Time DESC";
        }

        if (filter.getPageSize() != 0x7fffffff) {
            filterString += " LIMIT " + filter.getPageNumber() * filter.getPageSize() + "," + filter.getPageSize();
        }

        try {
            long timerStart = System.currentTimeMillis();

            // Retrieve the table array
            Statement st = connection.createStatement();

            String queryString = new String();
            queryString = "SELECT Time, Value FROM " + table;
            if (!filterString.isEmpty()) {
                queryString += filterString;
            }

            logger.debug("mySQL: query: {}", queryString);

            // Turn use of the cursor on.
            st.setFetchSize(50);

            ResultSet rs = st.executeQuery(queryString);

            long count = 0;
            List<HistoricItem> items = new ArrayList<HistoricItem>();
            State state;
            while (rs.next()) {
                count++;

                if (item instanceof NumberItem) {
                    state = new DecimalType(rs.getDouble(2));
                } else if (item instanceof ColorItem) {
                    state = new HSBType(rs.getString(2));
                } else if (item instanceof DimmerItem) {
                    state = new PercentType(rs.getInt(2));
                } else if (item instanceof SwitchItem) {
                    state = OnOffType.valueOf(rs.getString(2));
                } else if (item instanceof ContactItem) {
                    state = OpenClosedType.valueOf(rs.getString(2));
                } else if (item instanceof RollershutterItem) {
                    state = new PercentType(rs.getInt(2));
                } else if (item instanceof DateTimeItem) {
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTimeInMillis(rs.getTimestamp(2).getTime());
                    state = new DateTimeType(calendar);
                } else {
                    state = new StringType(rs.getString(2));
                }

                MysqlItem mysqlItem = new MysqlItem(itemName, state, rs.getTimestamp(1));
                items.add(mysqlItem);
            }

            rs.close();
            st.close();

            long timerStop = System.currentTimeMillis();
            logger.debug("mySQL: query returned {} rows in {} ms", count, timerStop - timerStart);

            // Success
            errCnt = 0;

            return items;
        } catch (SQLException e) {
            errCnt++;
            logger.error("mySQL: Error running querying.", e);
        }
        return Collections.emptySet();
    }

    @Override
    public @NonNull Set<@NonNull PersistenceItemInfo> getItemInfo() {
        return Collections.emptySet();
    }

    @Override
    public List<PersistenceStrategy> getDefaultStrategies() {
        return Collections.emptyList();
    }
}
